<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Member_model extends CI_Model {

	function __construct() {
		parent::__construct();
		
	}

	/**
	 * 가입중인 상품, 캐쉬 상태 가져오기
	 */
	function get_summary($user_id) {
		log_message('debug',"#### Member_model::get_summary($user_id)");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       M.user_name ";
		$sql .= PHP_EOL."     , M.nickname ";
		$sql .= PHP_EOL."     , M.sex ";
		$sql .= PHP_EOL."     , M.birthdate ";
		$sql .= PHP_EOL."     , M.birthtime ";
		$sql .= PHP_EOL."     , M.birth_solunar ";
		$sql .= PHP_EOL."     , M.cl_year ";
		$sql .= PHP_EOL."     , (SELECT class_name FROM tb_member_class_code WHERE class_cd = (IF(PM.package_cd > 1,'PRE','GEN')) ) class_name ";
		$sql .= PHP_EOL."     , P.cash ";
		$sql .= PHP_EOL."     , P.point ";
		$sql .= PHP_EOL."     , (P.cash+P.point) cashpoint ";
		$sql .= PHP_EOL."     , IFNULL(PM.package_cd, '1') package_cd ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = IFNULL(PM.package_cd, '1')) package_name ";
		$sql .= PHP_EOL."     , IFNULL(PM.vip_coupon_qty, '0') vip_coupon_qty ";
		$sql .= PHP_EOL."     , PM.start_dt ";
		$sql .= PHP_EOL."     , PM.end_dt ";
		$sql .= PHP_EOL."     , (SELECT CONCAT(file_url,raw_name,'_SIZE',file_ext) FROM tb_member_file WHERE idx = M.idx) member_img_url ";
		$sql .= PHP_EOL." FROM  tb_member M ";
		$sql .= PHP_EOL." INNER JOIN tb_point P ON M.user_id = P.user_id ";
		$sql .= PHP_EOL." LEFT OUTER JOIN (SELECT * FROM tb_member_package WHERE DATE_FORMAT(CURDATE(),'%Y%m%d') BETWEEN start_dt AND end_dt) PM ON M.user_id = PM.user_id ";
		$sql .= PHP_EOL." WHERE M.user_id = ? ";
		$query = $this->db->query($sql, array($user_id)) or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * 회원정보 가져오기
	 */
	function get_member($user_id) {
		log_message('debug',"#### Member_model::get_member($user_id)");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       M.* ";
		$sql .= PHP_EOL."     , (SELECT class_name FROM tb_member_class_code WHERE class_cd = (IF(PM.package_cd > 1,'GEN','PRE')) ) class_name ";
		$sql .= PHP_EOL."     , P.cash ";
		$sql .= PHP_EOL."     , P.point ";
		$sql .= PHP_EOL."     , (P.cash+P.point) cashpoint ";
		$sql .= PHP_EOL."     , IFNULL(PM.package_cd, '1') package_cd ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = IFNULL(PM.package_cd, '1')) package_name ";
		$sql .= PHP_EOL."     , IFNULL(PM.vip_coupon_qty, '0') vip_coupon_qty ";
		$sql .= PHP_EOL."     , PM.start_dt ";
		$sql .= PHP_EOL."     , PM.end_dt ";
		$sql .= PHP_EOL." FROM  tb_member M ";
		$sql .= PHP_EOL." INNER JOIN tb_point P ON M.user_id = P.user_id ";
		$sql .= PHP_EOL." LEFT OUTER JOIN (SELECT * FROM tb_member_package WHERE DATE_FORMAT(CURDATE(),'%Y%m%d') BETWEEN start_dt AND end_dt) PM ON M.user_id = PM.user_id ";
		$sql .= PHP_EOL." WHERE M.user_id = ? ";
		$query = $this->db->query($sql, array($user_id)) or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * 회원의 사용중인 패키지 정보 가져오기
	 */
	function get_member_package($user_id) {
		log_message('debug',"#### Member_model::get_member_package($user_id)");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       PM.idx ";
		$sql .= PHP_EOL."     , IFNULL(PM.package_cd, '1') package_cd ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = IFNULL(PM.package_cd, '1')) package_name ";
		$sql .= PHP_EOL."     , IFNULL(PM.vip_coupon_qty, '0') vip_coupon_qty ";
		$sql .= PHP_EOL."     , (SELECT vip_coupon_qty FROM tb_package WHERE package_cd = PM.package_cd) package_vip_coupon_qty ";
		$sql .= PHP_EOL."     , PM.giver_id ";
		$sql .= PHP_EOL."     , PM.start_dt ";
		$sql .= PHP_EOL."     , PM.end_dt ";
		$sql .= PHP_EOL." FROM  tb_member M ";
		$sql .= PHP_EOL." LEFT OUTER JOIN (SELECT * FROM tb_member_package WHERE DATE_FORMAT(CURDATE(),'%Y%m%d') BETWEEN start_dt AND end_dt) PM ON M.user_id = PM.user_id ";
		$sql .= PHP_EOL." WHERE M.user_id = ? ";
		$query = $this->db->query($sql, array($user_id)) or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}
	
	/**
	 * 회원의 보유 쿠폰 정보 가져오기
	 */
	function select_member_package_coupon_list($idx) {
		log_message('debug',"#### Member_model::select_member_package_coupon_list($idx)");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       P.idx ";
		$sql .= PHP_EOL."     , P.package_cd ";
		$sql .= PHP_EOL."     , C.service_group_id ";
		$sql .= PHP_EOL."     , (SELECT service_group_name FROM tb_service_group WHERE service_group_id = C.service_group_id) service_group_name ";
		$sql .= PHP_EOL."     , C.qty ";
		$sql .= PHP_EOL."     , (SELECT qty FROM tb_package_coupon WHERE package_cd = P.package_cd AND service_group_id = C.service_group_id) package_coupon_qty ";
		$sql .= PHP_EOL." FROM tb_member_package P, tb_member_package_coupon C ";
		$sql .= PHP_EOL." WHERE P.idx = C.idx ";
		$sql .= PHP_EOL." AND   P.idx = ? ";
		$query = $this->db->query($sql, array($idx)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}
	
	/**
	 * 회원의 보유 포인트 정보 가져오기
	 */
	function get_member_point($user_id) {
		log_message('debug',"#### Member_model::get_member_point($user_id)");

		$query = $this->db->get_where('tb_point', array('user_id'=>$user_id)) or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * VIP이용권 선물 내역
	 */
	function select_vip_given_history_list($member_package_idx) {
		log_message('debug',"#### Member_model::select_vip_given_history_list($member_package_idx)");
		
		$query = $this->db->get_where('tb_vip_given_history', array('member_package_idx'=>$member_package_idx)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}
}

/* End of file member_model.php */
/* Location: ./application/models/mypage/member_model.php */